﻿using Freshbox2._0.BLL;
using Freshbox2._0.MODEL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Web.Mvc;
using PagedList;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HSSF.Util;
using System.Text;

namespace Freshbox2._0.UI.Controllers
{
    public class SaleDetailController : BaseController<usersorder>
    {
        //
        // GET: /SaleDetail/
        /// <summary>
        /// 销售明细统计
        /// </summary>
        /// <returns></returns>
        protected admin CurrentUer
        {
            get { return Session["CurrentUer"] == null ? null : (admin)Session["CurrentUer"]; }
        }
        public override BLL.BaseBll<usersorder> bll
        {
            get
            {
                return new UsersOrderBll();
            }

        }

        OrderDetailsBll orderDetailBll = new OrderDetailsBll();

        StoreBll storeBll = new StoreBll();

        WaresBll waresBll = new WaresBll();

        public class ListSaleDetail
        {
            public string waresCode { get; set; }
            public string waresName { get; set; }
            public decimal waresPrice { get; set; }    //单价
            public decimal saleMoney { get; set; }   // 出售总金额
            public int saleNum { get; set; }   //出售数量
            public decimal waresBid { get; set; }
            public decimal buyMoney { get; set; } 
            public decimal ProfitMoney { get; set; } //盈利金额
            public string ProfitRate { get; set; } //利润率
            public int storeid { get; set; } 
            public double ProfitRateHidden { get; set; } 
        }

        public class ListWare
        {
            public string waresCode { get; set; }
            public string waresName { get; set; }
            public long saleNum { get; set; }
            public decimal? waresPrice { get; set; }
            public decimal? waresBid { get; set; }
        }

        public ActionResult GetShopName()
        {
            return Json(storeBll.Search(x=>x.storeStatus==1).Select(x => new { shopID = x.storeid, shopName = x.storeName }).ToList());
        }
     
        public class detail
        {
            public long saleNum;
            public string startTime;
            public string endTime;
        }

        public class StoreDetail
        {
            public long saleNum;
            public int? storeid;
            public string storeName;
        }

        public class OrderDetailList
        {
            public long orderDetailsID;
            public int? storeid;
            public string warescode;
            public long waresQuantity;
        }
        /// <summary>
        /// 点击加号 之前的   一周的销量
        /// </summary>
        /// <param name="waresCode"></param>
        /// <returns></returns>
        public ActionResult GetDetailByWaresCode(string waresCode)
        {
            long num = 0;
            var detailList = new List<detail>();
            //查询 今天之前 一周的 销量  
            for (var j = 0; j <= 3; j++)
            {
                DateTime date3 = DateTime.Now.Date.AddDays(-(7 * j));// 今天
                DateTime date = date3.AddDays(+1);//2.11 今天+1 天  
                DateTime date2 = date3.AddDays(-7);   // 一周前的记录   saleNum   startTime endTime -1
                var orderList = bll.Search(x => x.orderTime >= date2 && x.orderTime <= date && x.orderStatus == 1).Select(x => new { x.orderCode, }).ToList();
                foreach (var item in orderList)
                {

                    var orderDetList2 = orderDetailBll.Search(x => x.orderCode == item.orderCode && x.waresCode == waresCode).GroupBy(x => x.waresCode).Select(x => new { SaleNum = x.Sum(y => y.waresQuantity) }).ToList();
                    if (orderDetList2.Count != 0)
                    {
                        num += orderDetList2.FirstOrDefault().SaleNum;
                    }

                }
                detail detailModel = new detail();
                detailModel.saleNum = num;
                detailModel.startTime = date3.ToShortDateString();
                detailModel.endTime = date2.ToShortDateString();
                detailList.Add(detailModel);

                num = 0;

                //var orderDetList = orderDetailBll.Search(x => x.orderTime >= date2 && x.orderTime <= date && x.waresCode == waresCode).GroupBy(x => x.waresCode).Select(x => new { SaleNum = x.Sum(y => y.waresQuantity) }).ToList();
                //if (orderDetList.Count() != 0)
                //{
                //detail detailModel = new detail();
                //detailModel.saleNum = num;
                //detailModel.startTime = date3.ToShortDateString();
                //detailModel.endTime = date2.ToShortDateString();
                //detailList.Add(detailModel);
                //}

                //else
                //{
                //detail detailModel = new detail();
                //detailModel.saleNum = 0;
                //detailModel.startTime = date3.ToShortDateString();
                //detailModel.endTime = date2.ToShortDateString();
                //detailList.Add(detailModel);
                //}

            }
            return Json(new { rows = detailList }, JsonRequestBehavior.AllowGet);
        }


        /// <summary>
        /// 点击加号 显示个店铺销量      
        /// </summary>   
        /// <param name="waresCode"></param>
        /// <returns></returns>
        public ActionResult GetDetailEf(string waresCode, string date1, string date2)
        {
            var detailList = new List<StoreDetail>();

            var detail = new List<OrderDetailList>();

            Expression<Func<orderdetails, bool>> condition = null;

            //默认今天的
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2))
            {
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                //查询满足时间的订单详情信息   
                condition = x => x.orderTime <= todayNow && x.orderTime >= midnight && x.usersorder.orderStatus == 1 && x.waresCode == waresCode;
                var orderdetailList = orderDetailBll.Search().Where(condition).GroupBy(x => x.storeid).Select(x => new { SaleNum = x.Sum(y => y.waresQuantity), x.FirstOrDefault().storeid, }).ToList();
                //
                foreach (var item in orderdetailList)
                {
                    StoreDetail detailModel = new StoreDetail();
                    detailModel.saleNum = item.SaleNum;
                    detailModel.storeid = item.storeid;
                    detailModel.storeName = storeBll.Search(x => x.storeid == item.storeid).Select(x => new { x.storeName }).FirstOrDefault().storeName;
                    detailList.Add(detailModel);

                }
            }
            //只按照时间查询     
            if (date1 != null && date1 != "" && date2 != null && date2 != "")
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.waresCode == waresCode && x.orderTime >= datetime1 && x.orderTime <= datetime2 && x.usersorder.orderStatus == 1;
                var orderdetailList = orderDetailBll.Search().Where(condition).GroupBy(x => x.storeid).Select(x => new { SaleNum = x.Sum(y => y.waresQuantity), x.FirstOrDefault().storeid, }).ToList();
                //
                foreach (var item in orderdetailList)
                {
                    StoreDetail detailModel = new StoreDetail();
                    detailModel.saleNum = item.SaleNum;
                    detailModel.storeid = item.storeid;
                    detailModel.storeName = storeBll.Search(x => x.storeid == item.storeid).Select(x => new { x.storeName }).FirstOrDefault().storeName;
                    detailList.Add(detailModel);
                }
            }
            return Json(new { rows = detailList }, JsonRequestBehavior.AllowGet);
        }


        public decimal SaleMoney=0;

        public decimal BuyMoney = 0;

        /// <summary>
        ///  统计总的   收入
        /// </summary>

        public ActionResult GetRateAll(string storeid, string waresName, string date1, string date2)
        {
            // 1.先筛选出这个时间段的 满足要求的  订单信息 
            StringBuilder sql = new StringBuilder();
            sql.Append(" select  (wares.waresBid *SUM(waresQuantity))as buyMoney, orderdetails.waresPrice*SUM(waresQuantity) as saleMoney from orderdetails LEFT JOIN usersorder on orderdetails.orderCode=usersorder.orderCode INNER JOIN wares on orderdetails.waresCode=wares.waresCode   where usersorder.orderStatus=1    ");
            //只按照   店铺   查询 默认是今天某店铺的订单情况
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                sql.Append("and orderdetails.orderTime BETWEEN '" + midnight + " ' and '" + todayNow + "'  and orderdetails.storeid='" + shopid + "' GROUP BY orderdetails.waresCode  ");
            }
            //按照  时间和店铺 查询  
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append("and orderdetails.orderTime BETWEEN '" + datetime1 + " ' and '" + datetime2 + "'  and orderdetails.storeid='" + shopid + "' GROUP BY orderdetails.waresCode  ");
            }
            //只按照时间查询     
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid == "")
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append("and orderdetails.orderTime BETWEEN '" + datetime1 + " ' and '" + datetime2 + "'   GROUP BY orderdetails.waresCode  ");
            }
            //默认情况   默认查询本天的所有记录   
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                sql.Append("and orderdetails.orderTime BETWEEN '" + midnight + " ' and '" + todayNow + "'  GROUP BY orderdetails.waresCode ");
            }
            ////计算订单总金额  
            var a = bll.SearchBySQL<ListSaleDetail>(sql.ToString()).ToList();
            BuyMoney=  a.Sum(x => x.buyMoney);
            SaleMoney = a.Sum(x => x.saleMoney);
            return Json(new { SaleMoney = SaleMoney, BuyMoney = BuyMoney, AllProfitMoney = SaleMoney - BuyMoney, Rate = ((SaleMoney - BuyMoney) / SaleMoney).ToString("P") }, JsonRequestBehavior.AllowGet);
        }


        public int AllSum { get; set; }//出售总数 AllSaleMoney AllBuyMoney  ProfitMoney rate
        public decimal AllSaleMoney { get; set; }//出售总金额
        public decimal AllBuyMoney { get; set; }//进价总金额    
        public decimal ProfitMoney { get; set; }//盈利总金额    
        public string rate { get; set; }//平均利润率
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="storeid"></param>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        /// 
        //public FileResult ExportExcel(string storeid, string date1, string date2)
        //{
        //    Expression<Func<usersorder, bool>> condition = x => x.orderStatus == 1;
        //    string dat2a = DateTime.Now.ToLongDateString();
        //    // 1.先筛选出这个时间段的 满足要求的  订单信息  
        //    //默认情况   默认查询本天的所有记录  
        //    if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
        //    {
        //        DateTime todayNow = DateTime.Now;
        //        DateTime midnight = DateTime.Now;
        //        midnight = midnight.Date;
        //        condition = x => x.orderStatus == 1 && x.orderTime <= todayNow && x.orderTime >= midnight;
        //    }
        //    //只按照店铺查询 默认是今天某店铺的订单情况
        //    if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "")
        //    {
        //        int shopid = Convert.ToInt32(storeid);
        //        DateTime todayNow = DateTime.Now;
        //        DateTime midnight = DateTime.Now;
        //        midnight = midnight.Date;
        //        condition = x => x.orderStatus == 1 && x.orderTime <= todayNow && x.orderTime >= midnight && x.storeid == shopid;
        //    }
        //    //按照时间和店铺查询
        //    if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != null)
        //    {
        //        int shopid = Convert.ToInt32(storeid);
        //        DateTime datetime1 = DateTime.Parse(date1);
        //        DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
        //        condition = x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2 && x.storeid == shopid;
        //    }
        //    //只按照时间查询 
        //    if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid == null)
        //    {
        //        DateTime datetime1 = DateTime.Parse(date1);
        //        DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
        //        condition = x => x.orderStatus == 1 && x.orderTime >= datetime1 && x.orderTime <= datetime2;
        //    }

        //    var orderList = bll.Search().Where(condition).Select(x => new { x.orderTime, x.orderCode, x.orderSumprice, x.userCode }).ToList();

        //    var listSale = new List<ListSaleDetail>();

        //    var orderByList = new List<ListSaleDetail>();

        //    var listWare = new List<ListWare>();

        //    //1.遍历orderList   
        //    foreach (var oneOrderCode in orderList)
        //    {
        //        //2.根据订单编号 查询 订单详情表 相关记录信息  所有的记录  只是对一个订单所涉及到的商品进行分类

        //        var orderdetailList = orderDetailBll.Search(x => x.orderCode == oneOrderCode.orderCode).GroupBy(x => x.waresCode).Select(x => new { x.FirstOrDefault().waresCode, saleNum = x.Sum(y => y.waresQuantity) }).ToList();

        //        if (orderdetailList.Count != 0)
        //        {
        //            foreach (var item in orderdetailList)
        //            {
        //                ListWare modelAllWare = new ListWare();
        //                modelAllWare.waresCode = item.waresCode;
        //                modelAllWare.saleNum = item.saleNum;
        //                listWare.Add(modelAllWare);  ///这就是所有的商品的汇总 形成一个新的订单商品详情表
        //            }
        //        }
        //    }
        //    //3.遍历listWare   然后再对每一种商品进行统计   
        //    var wareOrderList = listWare.GroupBy(x => x.waresCode).Select(x => new { x.FirstOrDefault().waresCode, saleNum = x.Sum(y => y.saleNum) }).ToList();
        //    foreach (var oneWareCode in wareOrderList)
        //    {
        //        //4. 根据订单详情表  的 商品编号  查询商品表的相关信息
        //        var wareList = waresBll.Search(x => x.waresCode == oneWareCode.waresCode).GroupBy(x => x.waresCode).Select(x => new { x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, waresPrice = x.FirstOrDefault().waresPrice, x.FirstOrDefault().waresBid, }).ToList();

        //        if (wareList.Count != 0)
        //        {
        //            ListSaleDetail salePro = new ListSaleDetail();
        //            salePro.waresCode = wareList.FirstOrDefault().waresCode;
        //            salePro.waresName = wareList.FirstOrDefault().waresName;
        //            salePro.waresPrice = Convert.ToDecimal(wareList.FirstOrDefault().waresPrice);
        //            salePro.saleNum = Convert.ToInt32(oneWareCode.saleNum);
        //            salePro.saleMoney = Convert.ToDecimal((wareList.FirstOrDefault().waresPrice) * salePro.saleNum);
        //            salePro.buyonePrice = Convert.ToDecimal(wareList.FirstOrDefault().waresBid);
        //            salePro.buyMoney = Convert.ToDecimal((wareList.FirstOrDefault().waresBid) * salePro.saleNum);
        //            salePro.ProfitMoney = salePro.saleMoney - salePro.buyMoney;
        //            salePro.ProfitRate = ((salePro.ProfitMoney / salePro.saleMoney)).ToString("p");
        //            listSale.Add(salePro);
        //            AllSum = AllSum + salePro.saleNum;
        //            AllSaleMoney = AllSaleMoney + salePro.saleMoney;
        //            AllBuyMoney = AllBuyMoney + salePro.buyMoney;
        //            ProfitMoney = ProfitMoney = salePro.ProfitMoney;
        //            rate = (ProfitMoney / AllSaleMoney).ToString("p");
        //        }
        //    }
        //    //  //创建一个工作簿     
        //    NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
        //    //添加一个sheet     //创建一个页
        //    NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
        //    //设置单元格 的宽高
        //    sheet1.DefaultColumnWidth = 1 * 25;  //宽度
        //    sheet1.DefaultRowHeightInPoints = 25;  //高度
        //    //创建一行
        //    IRow row = sheet1.CreateRow(0);
        //    //创建一列
        //    ICell cell = row.CreateCell(0);

        //    ICellStyle cellStyle = book.CreateCellStyle();////创建样式对象
        //    cellStyle.FillForegroundColor = 14;  //具体数字代表的颜色看NPOI颜色对照表
        //    cellStyle.FillPattern = FillPattern.SolidForeground;
        //    //水平对齐
        //    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
        //    //垂直对齐
        //    cellStyle.VerticalAlignment = VerticalAlignment.Center;
        //    cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Blue.Index;

        //    cellStyle.TopBorderColor = HSSFColor.DarkGreen.Blue.Index;


        //    IFont font = book.CreateFont(); //创建一个字体样式对象
        //    font.FontName = "方正舒体"; //和excel里面的字体对应
        //    font.FontHeightInPoints = 25;//字体大小
        //    font.Color = HSSFColor.DarkGreen.Blue.Index;

        //    cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        //    cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        //    cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        //    cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;


        //    // font.Boldweight = short.MaxValue;//字体加粗  
        //    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
        //    cellStyle.SetFont(font); //将字体样式赋给样式对象
        //    cell.CellStyle = cellStyle; //把样式赋给单元格

        //    //给sheet1添加第一行的头部标题  
        //    NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
        //    row1.CreateCell(0).SetCellValue("商品编号");
        //    row1.CreateCell(1).SetCellValue("商品名称");
        //    row1.CreateCell(2).SetCellValue("销售数量");
        //    row1.CreateCell(3).SetCellValue("商品售价");
        //    row1.CreateCell(4).SetCellValue("出售总金额");
        //    row1.CreateCell(5).SetCellValue("商品进价");
        //    row1.CreateCell(6).SetCellValue("进价总金额");
        //    row1.CreateCell(7).SetCellValue("盈利总金额");
        //    row1.CreateCell(8).SetCellValue("销售利润率");
        //    //将数据逐步写入sheet1各个行
        //    for (int i = 0; i < listSale.Count; i++)
        //    {
        //        NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
        //        //创建单元格并设置它的值 ID
        //        rowtemp.CreateCell(0).SetCellValue(listSale[i].waresCode);
        //        rowtemp.CreateCell(1).SetCellValue(listSale[i].waresName);
        //        rowtemp.CreateCell(2).SetCellValue(listSale[i].saleNum);
        //        rowtemp.CreateCell(3).SetCellValue(listSale[i].waresPrice.ToString());
        //        rowtemp.CreateCell(4).SetCellValue(listSale[i].saleMoney.ToString());
        //        rowtemp.CreateCell(5).SetCellValue(listSale[i].buyonePrice.ToString());
        //        rowtemp.CreateCell(6).SetCellValue(listSale[i].buyMoney.ToString());
        //        rowtemp.CreateCell(7).SetCellValue(listSale[i].ProfitMoney.ToString());
        //        rowtemp.CreateCell(8).SetCellValue(listSale[i].ProfitRate);

        //    }
        //    //------ko  添加合计行成功     样式设置不成功
        //    //合计列   AllSaleMoney AllBuyMoney  ProfitMoney rate AllSum
        //    row = sheet1.CreateRow((sheet1.LastRowNum + 1));//在工作表中添加一行
        //    // 创建单元格
        //    ICell cell1 = row.CreateCell(0);
        //    ICell cell2 = row.CreateCell(1);
        //    ICell cell3 = row.CreateCell(2);
        //    ICell cell4 = row.CreateCell(3);
        //    ICell cell5 = row.CreateCell(4);
        //    ICell cell6 = row.CreateCell(5);
        //    ICell cell7 = row.CreateCell(6);
        //    ICell cell8 = row.CreateCell(7);
        //    ICell cell9 = row.CreateCell(8);
        //    ICell cell10 = row.CreateCell(9);
        //    //cell1.SetCellValue("总销量");//赋值
        //    //cell2.SetCellValue(AllSum);//赋值
        //    //cell3.SetCellValue("销售总金额");//赋值
        //    //cell4.SetCellValue(AllSaleMoney.ToString());//赋值
        //    //cell5.SetCellValue("进价总金额");//赋值
        //    //cell6.SetCellValue(AllBuyMoney.ToString());//赋值
        //    //cell7.SetCellValue("盈利总金额");//赋值
        //    //cell8.SetCellValue(ProfitMoney.ToString());//赋值 
        //    //cell9.SetCellValue("平均利润率");//赋值
        //    //cell10.SetCellValue(rate);//赋值
        //    // 写入到客户端 
        //    System.IO.MemoryStream ms = new System.IO.MemoryStream();
        //    book.Write(ms);
        //    ms.Seek(0, SeekOrigin.Begin);
        //    //string dataFile = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        //    string dataFile = DateTime.Now.ToLocalTime().ToString();
        //    string a = DateTime.Now.GetDateTimeFormats('g')[0].ToString();
        //    return File(ms, "application/vnd.ms-excel", a + "销售明细统计.xls");


        //}



        /// <summary>
        /// 商品销售明细列表    SQL语句优化
        /// </summary>
        /// <returns></returns>

        public ActionResult GetAllBySql22(int page, int rows, string storeid, string waresName, string date1, string date2, string sort, string order)
        {
            var listSale = new List<ListSaleDetail>();

            var orderByList = new List<ListSaleDetail>();

            // 1.先筛选出这个时间段的 满足要求的  订单信息 

            StringBuilder sql = new StringBuilder();
            sql.Append(" select SUM(waresQuantity) as saleNum  ,wares.waresName,wares.waresBid, wares.waresBid *SUM(waresQuantity) as buyMoney, orderdetails.waresPrice*SUM(waresQuantity) as saleMoney,orderdetails.waresCode,orderdetails.waresPrice,orderdetails.storeid  from orderdetails inner join usersorder on orderdetails.orderCode=usersorder.orderCode INNER JOIN wares on orderdetails.waresCode=wares.waresCode where usersorder.orderStatus=1  ");

            //只按照   店铺   查询 默认是今天某店铺的订单情况
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                sql.Append("and orderdetails.orderTime BETWEEN '" + midnight + " ' and '" + todayNow + "'  and orderdetails.storeid='" + shopid + "' ");
            }
            //按照  时间和店铺 查询
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append("and orderdetails.orderTime BETWEEN '" + datetime1 + " ' and '" + datetime2 + "'  and orderdetails.storeid='" + shopid + "'  ");
            }
            //只按照时间查询      
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid == "")
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql.Append("and orderdetails.orderTime BETWEEN '" + datetime1 + " ' and '" + datetime2 + "' ");
            }
            //默认情况   默认查询本天的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now;
                midnight = midnight.Date;
                sql.Append("and orderdetails.orderTime BETWEEN '" + midnight + " ' and '" + todayNow + "' ");
            }
            if (!string.IsNullOrEmpty(waresName))
            {
                sql.Append("and waresName like '%" + waresName + "%' ");
            }
            sql.Append(" GROUP BY waresCode   ");
            int start = (page - 1) * rows;//从第几条 开始  rows  取多少条记录
            int count = bll.SearchBySQL<ListSaleDetail>(sql.ToString()).Count();
            if (sort == "saleNum" && order == "asc")
            {
                sql.Append(" ORDER BY saleNum DESC limit " + start + "," + rows + " ");              
            }
            if (sort == "saleMoney" && order == "asc")
            {
                sql.Append(" ORDER BY saleMoney ASC limit " + start + "," + rows + " ");             
            }
            if (sort == "saleNum" && order == "desc")
            {
                sql.Append(" ORDER BY saleNum DESC limit " + start + "," + rows + " ");             
            }
            if (sort == "saleMoney" && order == "desc")
            {
                sql.Append(" ORDER BY saleMoney ASC limit " + start + "," + rows + " ");             
            }
            var a = bll.SearchBySQL<ListSaleDetail>(sql.ToString()).ToList();
            ListSaleDetail salePro = null;
            foreach (var item in a)
            {
                salePro = new ListSaleDetail();
                salePro.waresCode = item.waresCode;
                salePro.waresName = item.waresName;
                salePro.waresPrice = Convert.ToDecimal(item.waresPrice);
                salePro.saleNum = Convert.ToInt32(item.saleNum);
                salePro.saleMoney = Convert.ToDecimal(item.saleMoney);
                salePro.waresBid = Convert.ToDecimal(item.waresBid);
                salePro.buyMoney = Convert.ToDecimal(item.buyMoney);
                salePro.ProfitMoney = salePro.saleMoney - salePro.buyMoney;
                salePro.ProfitRateHidden = Convert.ToDouble(salePro.ProfitMoney / salePro.saleMoney);
                salePro.ProfitRate = ((salePro.ProfitMoney / salePro.saleMoney)).ToString("p");
                listSale.Add(salePro);
            }
            if (sort == "ProfitRate" && order == "desc")
            {
                listSale = listSale.OrderByDescending(x => x.ProfitRateHidden).ToPagedList(page, rows).ToList();
            }
            if (sort == "ProfitRate" && order == "asc")
            {
                listSale = listSale.OrderBy(x => x.ProfitRateHidden).ToPagedList(page, rows).ToList();
            }

            return Json(new { rows = listSale, total = count });
        }
    }
}
